import mysql.connector
from mysql.connector import Error
from app.config.db_config import DB_CONFIG
from app.model.course_model import Course

class CourseRepository:
    """图书仓库类 - 负责所有与图书相关的数据库操作"""
    
    # 表名常量
    TABLE_NAME = "t_courses"
    
    def __init__(self):
        """初始化图书仓库"""
        pass
    
    def get_connection(self):
        """获取数据库连接"""
        try:
            connection = mysql.connector.connect(**DB_CONFIG)
            return connection
        except Error as e:
            print(f"数据库连接失败: {e}")
            return None
    
    def save(self, course):
        """保存图书信息到数据库"""
        # 验证图书信息
        course.validate()
        
        connection = self.get_connection()
        if not connection:
            return False
        
        try:
            cursor = connection.cursor()
            if course.id:
                # 更新操作
                query = f"""UPDATE {self.TABLE_NAME} 
                           SET name = %s, credit = %s 
                           WHERE id = %s"""
                cursor.execute(query, (course.name, course.credit, course.id))
            else:
                # 插入操作
                query = f"""INSERT INTO {self.TABLE_NAME} (name, credit) 
                           VALUES (%s, %s)"""
                cursor.execute(query, (course.name, course.credit))
                course.id = cursor.lastrowid
            
            connection.commit()
            return True
        except Error as e:
            print(f"保存图书失败: {e}")
            connection.rollback()
            return False
        finally:
            if connection.is_connected():
                cursor.close()
                connection.close()
    
    def get_by_id(self, course_id):
        """根据ID获取图书"""
        connection = self.get_connection()
        if not connection:
            return None
        
        try:
            cursor = connection.cursor(dictionary=True)
            query = f"SELECT * FROM {self.TABLE_NAME} WHERE id = %s"
            cursor.execute(query, (course_id,))
            row = cursor.fetchone()
            
            if row:
                return Course(**row)
            return None
        except Error as e:
            print(f"获取图书失败: {e}")
            return None
        finally:
            if connection.is_connected():
                cursor.close()
                connection.close()
    
    def get_all(self):
        """获取所有图书"""
        connection = self.get_connection()
        if not connection:
            return []
        
        try:
            cursor = connection.cursor(dictionary=True)
            query = f"SELECT * FROM {self.TABLE_NAME}"
            cursor.execute(query)
            rows = cursor.fetchall()
            
            return [Course(**row) for row in rows]
        except Error as e:
            print(f"获取所有图书失败: {e}")
            return []
        finally:
            if connection.is_connected():
                cursor.close()
                connection.close()
    
    def delete(self, course_id):
        """删除图书"""
        if not course_id:
            raise ValueError("图书ID不能为空")
        
        connection = self.get_connection()
        if not connection:
            return False
        
        try:
            cursor = connection.cursor()
            query = f"DELETE FROM {self.TABLE_NAME} WHERE id = %s"
            cursor.execute(query, (course_id,))
            connection.commit()
            return cursor.rowcount > 0
        except Error as e:
            print(f"删除图书失败: {e}")
            connection.rollback()
            return False
        finally:
            if connection.is_connected():
                cursor.close()
                connection.close()
    
    def update(self, course_id, name=None, credit=None):
        """更新图书信息"""
        if not course_id:
            raise ValueError("图书ID不能为空")
        
        # 检查是否存在该图书
        existing_course = self.get_by_id(course_id)
        if not existing_course:
            return False
        
        # 创建更新对象
        update_course = Course(
            id=course_id,
            name=name if name is not None else existing_course.name,
            credit=credit if credit is not None else existing_course.credit
        )
        
        # 验证并保存更新
        return self.save(update_course)
    
    def search_by_name(self, keyword):
        """根据名称关键字搜索图书"""
        if not keyword:
            return self.get_all()
        
        connection = self.get_connection()
        if not connection:
            return []
        
        try:
            cursor = connection.cursor(dictionary=True)
            query = f"SELECT * FROM {self.TABLE_NAME} WHERE name LIKE %s"
            cursor.execute(query, (f"%{keyword}%",))
            rows = cursor.fetchall()
            
            return [Course(**row) for row in rows]
        except Error as e:
            print(f"搜索图书失败: {e}")
            return []
        finally:
            if connection.is_connected():
                cursor.close()
                connection.close()

# 创建仓库实例供外部使用
course_repository = CourseRepository()